from typing import Optional
from fastapi import APIRouter, Depends, Query
from pydantic import BaseModel, Field
from sqlalchemy import text
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

data_my_safe_06 = APIRouter(prefix="/phone", tags=["储物柜管理"], dependencies=[Depends(oauth2_scheme)])


class CabinetInventoryAlarmSearch(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    fbegdate: str = Field("2000-01-01", description="开始日期(yyyy-MM-dd)，默认'2000-01-01'")
    fenddate: str = Field("2000-01-01", description="结束日期(yyyy-MM-dd)，默认'2000-01-01'")
    fpointNo: str = Field("", description="点位编号")
    boxFequipmentbarcode: str = Field("", description="箱设备条码")
    cabFequipmentbarcode: str = Field("", description="柜设备条码")
    ftype: str = Field("106", description="报警类型编码，默认'106'")


@data_my_safe_06.get("/getDayPatrolAlarm", summary="储物柜-临期报警+安全库存询+先进先出接口")
def get_day_patrol_alarm(cabient: CabinetInventoryAlarmSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_day_report a"
    sql = sql + " left join lims.i_ts_cabinet b on b.fequipmentbarcode = a.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse c on c.fequipmentbarcode = a.fboxbarcode "
    sql = sql + " left join i_warehouse_inv d on d.frfidbarcode = a.frfidbarcode and d.fstate=3"
    sql = sql + " left join i_ts_good_info e on d.fgoodno = e.fgoodno"
    sql = sql + " left join i_ts_controlbox f on f.fcatbarcode=b.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=f.fequipmentbarcode"
    sql = sql + " left join a_ts_point s on b.fpoint_no = s.fpoint_no"
    sql = sql + " left join a_ts_site t on s.fsite_no = t.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fcheckdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
    param["ftakedateStart"] = cabient.fbegdate
    param["ftakedateEnd"] = cabient.fenddate
    sql = sql + " and a.ftype= :ftype "
    param["ftype"] = cabient.ftype
    if cabient.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabient.fsiteNo
    if cabient.fpointNo != "":
        sql = sql + " and s.fpoint_no= :fpointNo "
        param["fpointNo"] = cabient.fpointNo
    if cabient.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = cabient.boxFequipmentbarcode
    if cabient.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= :cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = cabient.cabFequipmentbarcode
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select to_char(a.fcheckdate,'yyyy-mm-dd hh24:mi:ss') fcheckdate,t.fsite_no,t.fsite_name  "
    sql = sql + " ,s.fpoint_name,g.fequipmenttag fconname,b.fequipmenttag fcabname, c.fequipmenttag fboxname"
    sql = sql + " ,a.frfidbarcode,e.fgoodno,e.fgoodname,d.fprocdate,a.fmessage"
    sql = sql + " from i_day_report a"
    sql = sql + " left join lims.i_ts_cabinet b on b.fequipmentbarcode = a.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse c on c.fequipmentbarcode = a.fboxbarcode "
    sql = sql + " left join i_warehouse_inv d on d.frfidbarcode = a.frfidbarcode and d.fstate=3"
    sql = sql + " left join i_ts_good_info e on d.fgoodno = e.fgoodno"
    sql = sql + " left join i_ts_controlbox f on f.fcatbarcode=b.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=f.fequipmentbarcode"
    sql = sql + " left join a_ts_point s on b.fpoint_no = s.fpoint_no"
    sql = sql + " left join a_ts_site t on s.fsite_no = t.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fcheckdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
    param["ftakedateStart"] = cabient.fbegdate
    param["ftakedateEnd"] = cabient.fenddate
    sql = sql + " and a.ftype= :ftype "
    param["ftype"] = cabient.ftype
    if cabient.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabient.fsiteNo
    if cabient.fpointNo != "":
        sql = sql + " and s.fpoint_no= :fpointNo "
        param["fpointNo"] = cabient.fpointNo
    if cabient.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = cabient.boxFequipmentbarcode
    if cabient.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= :cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = cabient.cabFequipmentbarcode
    if cabient.order == "desc":
        sql = sql + " order by  fcheckdate desc "
    else:
        sql = sql + " order by  fcheckdate asc "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = cabient.limit
    param["page"] = cabient.page
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fcheckdate'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['fpointName'] = data[key][3]
        d1['fconname'] = data[key][4]
        d1['fcabname'] = data[key][5]
        d1['fboxname'] = data[key][6]
        d1['frfidbarcode'] = data[key][7]
        d1['fgoodno'] = data[key][8]
        d1['fgoodname'] = data[key][9]
        d1['fprocdate'] = data[key][10]
        d1['fmessage'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": cabient.limit, "page": cabient.pageIndex,
                 "pages": (count + cabient.limit - 1) // cabient.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class CabinetFIFOSearch(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    fbegdate: str = Field("2000-01-01", description="开始日期(yyyy-MM-dd)，默认'2000-01-01'")
    fenddate: str = Field("2000-01-01", description="结束日期(yyyy-MM-dd)，默认'2000-01-01'")
    fpointNo: str = Field("", description="点位编号")
    boxFequipmentbarcode: str = Field("", description="箱设备条码")
    cabFequipmentbarcode: str = Field("", description="柜设备条码")
    ftype: str = Field("106", description="类型编码，默认'106'")


@data_my_safe_06.get("/getHardwareAlarm2", summary="储物柜-先进先出接口")
def get_hardware_alarm(cabient: CabinetFIFOSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_day_report a"
    sql = sql + " left join lims.i_ts_cabinet b on b.fequipmentbarcode = a.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse c on c.fequipmentbarcode = a.fboxbarcode "
    sql = sql + " left join i_warehouse_inv d on d.frfidbarcode = a.frfidbarcode and d.fstate=3"
    sql = sql + " left join i_ts_good_info e on d.fgoodno = e.fgoodno"
    sql = sql + " left join i_ts_controlbox f on f.fcatbarcode=b.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=f.fequipmentbarcode"
    sql = sql + " left join a_ts_point s on b.fpoint_no = s.fpoint_no"
    sql = sql + " left join a_ts_site t on s.fsite_no = t.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fcheckdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
    param["ftakedateStart"] = cabient.fbegdate
    param["ftakedateEnd"] = cabient.fenddate
    sql = sql + " and a.ftype= :ftype "
    param["ftype"] = cabient.ftype
    if cabient.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabient.fsiteNo
    if cabient.fpointNo != "":
        sql = sql + " and s.fpoint_no= :fpointNo "
        param["fpointNo"] = cabient.fpointNo
    if cabient.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = cabient.boxFequipmentbarcode
    if cabient.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= :cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = cabient.cabFequipmentbarcode
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select to_char(a.fcheckdate,'yyyy-mm-dd hh24:mi:ss') fcheckdate,t.fsite_no,t.fsite_name  "
    sql = sql + " ,s.fpoint_name,g.fequipmenttag fconname,b.fequipmenttag fcabname, c.fequipmenttag fboxname"
    sql = sql + " ,a.frfidbarcode,e.fgoodno,e.fgoodname,d.fprocdate,a.fmessage"
    sql = sql + " from i_day_report a"
    sql = sql + " left join lims.i_ts_cabinet b on b.fequipmentbarcode = a.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse c on c.fequipmentbarcode = a.fboxbarcode "
    sql = sql + " left join i_warehouse_inv d on d.frfidbarcode = a.frfidbarcode and d.fstate=3"
    sql = sql + " left join i_ts_good_info e on d.fgoodno = e.fgoodno"
    sql = sql + " left join i_ts_controlbox f on f.fcatbarcode=b.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=f.fequipmentbarcode"
    sql = sql + " left join a_ts_point s on b.fpoint_no = s.fpoint_no"
    sql = sql + " left join a_ts_site t on s.fsite_no = t.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fcheckdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
    param["ftakedateStart"] = cabient.fbegdate
    param["ftakedateEnd"] = cabient.fenddate
    sql = sql + " and a.ftype= :ftype "
    param["ftype"] = cabient.ftype
    if cabient.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabient.fsiteNo
    if cabient.fpointNo != "":
        sql = sql + " and s.fpoint_no= :fpointNo "
        param["fpointNo"] = cabient.fpointNo
    if cabient.boxFequipmentbarcode != "":
        sql = sql + " and a.fboxbarcode= :boxFequipmentbarcode "
        param["boxFequipmentbarcode"] = cabient.boxFequipmentbarcode
    if cabient.cabFequipmentbarcode != "":
        sql = sql + " and a.fcabbarcode= :cabFequipmentbarcode "
        param["cabFequipmentbarcode"] = cabient.cabFequipmentbarcode
    if cabient.order == "desc":
        sql = sql + " order by  fcheckdate desc "
    else:
        sql = sql + " order by  fcheckdate asc "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = cabient.limit
    param["offset"] = cabient.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fcheckdate'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['fpointName'] = data[key][3]
        d1['fconname'] = data[key][4]
        d1['fcabname'] = data[key][5]
        d1['fboxname'] = data[key][6]
        d1['frfidbarcode'] = data[key][7]
        d1['fgoodno'] = data[key][8]
        d1['fgoodname'] = data[key][9]
        d1['fprocdate'] = data[key][10]
        d1['fmessage'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": cabient.limit, "page": cabient.pageIndex,
                 "pages": (count + cabient.limit - 1) // cabient.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class CabinetSafetyStockSearch(PaginationBase):
    # 筛选条件
    fsiteNo: str = Field("", description="站点编号")
    fbegdate: str = Field("2000-01-01", description="开始日期(yyyy-MM-dd)，默认'2000-01-01'")
    fenddate: str = Field("2000-01-01", description="结束日期(yyyy-MM-dd)，默认'2000-01-01'")
    fpointNo: str = Field("", description="点位编号")
    ftype: str = Field("112", description="类型编码，默认'112'（安全库存）")


@data_my_safe_06.get("/getDayPatrolAlarm2", summary="储物柜-安全库存量")
def get_day_patrol_alarm2(cabient: CabinetSafetyStockSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_day_report a"
    sql = sql + " left join lims.i_ts_cabinet b on b.fequipmentbarcode = a.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse c on c.fequipmentbarcode = a.fboxbarcode "
    sql = sql + " left join i_warehouse_inv d on d.frfidbarcode = a.frfidbarcode and d.fstate=3"
    sql = sql + " left join i_ts_good_info e on d.fgoodno = e.fgoodno"
    sql = sql + " left join i_ts_controlbox f on f.fcatbarcode=b.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=f.fequipmentbarcode"
    sql = sql + " left join a_ts_point s on b.fpoint_no = s.fpoint_no"
    sql = sql + " left join a_ts_site t on s.fsite_no = t.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fcheckdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
    param["ftakedateEnd"] = cabient.fenddate
    param["ftakedateStart"] = cabient.fbegdate
    sql = sql + " and a.ftype= :ftype "
    param["ftype"] = cabient.ftype
    if cabient.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabient.fsiteNo
    if cabient.fpointNo != "":
        sql = sql + " and s.fpoint_no= :fpointNo "
        param["fpointNo"] = cabient.fpointNo
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.fcheckdate,t.fsite_no,t.fsite_name,s.fpoint_name,g.fequipmenttag fconname  "
    sql = sql + " ,b.fequipmenttag fcabname, c.fequipmenttag fboxname"
    sql = sql + " ,a.frfidbarcode,e.fgoodno,e.fgoodname,d.fprocdate,a.fmessage"
    sql = sql + " from i_day_report a"
    sql = sql + " left join lims.i_ts_cabinet b on b.fequipmentbarcode = a.fcabbarcode"
    sql = sql + " left join lims.i_ts_warehouse c on c.fequipmentbarcode = a.fboxbarcode "
    sql = sql + " left join i_warehouse_inv d on d.frfidbarcode = a.frfidbarcode and d.fstate=3"
    sql = sql + " left join i_ts_good_info e on d.fgoodno = e.fgoodno"
    sql = sql + " left join i_ts_controlbox f on f.fcatbarcode=b.fequipmentbarcode"
    sql = sql + " left join i_ts_box g on g.fequipmentbarcode=f.fequipmentbarcode"
    sql = sql + " left join a_ts_point s on b.fpoint_no = s.fpoint_no"
    sql = sql + " left join a_ts_site t on s.fsite_no = t.fsite_no"
    sql = sql + " where 1=1"
    sql = sql + " and to_char(a.fcheckdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
    param["ftakedateEnd"] = cabient.fenddate
    param["ftakedateStart"] = cabient.fbegdate
    sql = sql + " and a.ftype= :ftype "
    param["ftype"] = cabient.ftype
    if cabient.fsiteNo != "":
        sql = sql + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = cabient.fsiteNo
    if cabient.fpointNo != "":
        sql = sql + " and s.fpoint_no= :fpointNo "
        param["fpointNo"] = cabient.fpointNo
    if cabient.order == "desc":
        sql = sql + " order by  fcheckdate desc "
    else:
        sql = sql + " order by  fcheckdate asc "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = cabient.limit
    param["offset"] = cabient.offset
    data = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fcheckdate'] = data[key][0]
        d1['fsiteNo'] = data[key][1]
        d1['fsiteName'] = data[key][2]
        d1['fpointName'] = data[key][3]
        d1['fconname'] = data[key][4]
        d1['fcabname'] = data[key][5]
        d1['fboxname'] = data[key][6]
        d1['frfidbarcode'] = data[key][7]
        d1['fgoodno'] = data[key][8]
        d1['fgoodname'] = data[key][9]
        d1['fprocdate'] = data[key][10]
        d1['fmessage'] = data[key][11]
        data_list.append(d1)

    info_dict = {"list": data_list, "limit": cabient.limit, "page": cabient.pageIndex,
                 "pages": (count + cabient.limit - 1) // cabient.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)
